Imports System
Imports System.Web.Security
Imports System.Web
Imports System.Web.Configuration
Imports System.Security.Principal
Imports System.Security.Permissions
Imports System.Globalization
Imports System.Runtime.Serialization
Imports System.Collections
Imports System.Collections.Specialized
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports System.Security.Cryptography
Imports System.Text
Imports System.Text.RegularExpressions
Imports System.Configuration.Provider
Imports System.Configuration
Imports System.Web.Management
Imports System.Web.Util
Imports nsDataProvider = KDESolutions.Providers.DataProvider

Public NotInheritable Class MembershipProvider
    Inherits System.Web.Security.MembershipProvider

#Region "Private Properties"
    Private _EnablePasswordReset As Boolean
    Private _EnablePasswordRetrieval As Boolean
    Private _RequiresQuestionAndAnswer As Boolean
    Private _AppName As String
    Private _AppId As String
    Private _RequiresUniqueEmail As Boolean
    Private _MaxInvalidPasswordAttempts As Integer
    Private _CommandTimeout As Integer
    Private _PasswordAttemptWindow As Integer
    Private _MinRequiredPasswordLength As Integer
    Private _MinRequiredNonalphanumericCharacters As Integer
    Private _PasswordStrengthRegularExpression As String
    Private _SchemaVersionCheck As Integer
    Private _PasswordFormat As MembershipPasswordFormat
    Private _TablePrefix As String
    Private _DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString As String
    Private _ApplicationName As String
    Private Const PASSWORD_SIZE As Integer = 14
#End Region

#Region "Public Properties"
    ' Public properties
    Public Property MyDBType() As String
        Get
            Return _DBType
        End Get
        Set(ByVal value As String)
            _DBType = value
        End Set
    End Property

    Public Overrides ReadOnly Property EnablePasswordRetrieval() As Boolean
        Get
            Return _EnablePasswordRetrieval
        End Get
    End Property

    Public Overrides ReadOnly Property EnablePasswordReset() As Boolean
        Get
            Return _EnablePasswordReset
        End Get
    End Property

    Public Overrides ReadOnly Property RequiresQuestionAndAnswer() As Boolean
        Get
            Return _RequiresQuestionAndAnswer
        End Get
    End Property

    Public Overrides ReadOnly Property RequiresUniqueEmail() As Boolean
        Get
            Return _RequiresUniqueEmail
        End Get
    End Property

    Public Overrides ReadOnly Property PasswordFormat() As MembershipPasswordFormat
        Get
            Return _PasswordFormat
        End Get
    End Property

    Public Overrides ReadOnly Property MaxInvalidPasswordAttempts() As Integer
        Get
            Return _MaxInvalidPasswordAttempts
        End Get
    End Property

    Public Overrides ReadOnly Property PasswordAttemptWindow() As Integer
        Get
            Return _PasswordAttemptWindow
        End Get
    End Property

    Public Overrides ReadOnly Property MinRequiredPasswordLength() As Integer
        Get
            Return _MinRequiredPasswordLength
        End Get
    End Property

    Public Overrides ReadOnly Property MinRequiredNonAlphanumericCharacters() As Integer
        Get
            Return _MinRequiredNonalphanumericCharacters
        End Get
    End Property

    Public Overrides ReadOnly Property PasswordStrengthRegularExpression() As String
        Get
            Return _PasswordStrengthRegularExpression
        End Get
    End Property

    Public Overrides Property ApplicationName() As String
        Get
            Return _AppName
        End Get
        Set(ByVal value As String)
            If String.IsNullOrEmpty(value) Then
                Throw New ArgumentNullException("value")
            End If
            If (value.Length > 255) Then
                Throw New ProviderException("Provider application name too long")
            End If
            _AppName = value
        End Set
    End Property
#End Region

#Region "Membership Exposed Methods"
    Public Overrides Sub Initialize(ByVal name As String, ByVal config As NameValueCollection)
        ' Remove CAS from sample: HttpRuntime.CheckAspNetHostingPermission (AspNetHostingPermissionLevel.Low, SR.Feature_not_supported_at_this_level);
        If (config Is Nothing) Then
            Throw New ArgumentNullException("config")
        End If
        If String.IsNullOrEmpty(name) Then
            name = "DBMembershipProvider"
        End If
        If String.IsNullOrEmpty(config("description")) Then
            config.Remove("description")
            config.Add("description", "DBMembershipProvider")
        End If
        MyBase.Initialize(name, config)
        _SchemaVersionCheck = 0
        _EnablePasswordRetrieval = Convert.ToBoolean(GetConfigValue(config("enablePasswordRetrieval"), False))
        _EnablePasswordReset = Convert.ToBoolean(GetConfigValue(config("enablePasswordReset"), True))
        _RequiresQuestionAndAnswer = Convert.ToBoolean(GetConfigValue(config("requiresQuestionAndAnswer"), True))
        _RequiresUniqueEmail = Convert.ToBoolean(GetConfigValue(config("requiresUniqueEmail"), True))
        _MaxInvalidPasswordAttempts = Convert.ToInt64(GetConfigValue(config("maxInvalidPasswordAttempts"), 5))
        _PasswordAttemptWindow = Convert.ToInt64(GetConfigValue(config("passwordAttemptWindow"), 16))
        _MinRequiredPasswordLength = Convert.ToInt64(GetConfigValue(config("minRequiredPasswordLength"), 7))
        _MinRequiredNonalphanumericCharacters = Convert.ToInt64(GetConfigValue(config("minRequiredNonAlphanumericCharacters"), 1))
        _PasswordStrengthRegularExpression = config("passwordStrengthRegularExpression")
        _TablePrefix = GetConfigValue(config("tablePrefix"), "aspnet")
        _ApplicationName = GetConfigValue(config("applicationName"), GetDefaultAppName)
        'Start of config file settings for database connection ******
        If String.IsNullOrEmpty(config("dataProviderGroup")) Then
            Throw New HttpException("You must provide a dataProviderGroup")
        Else
            Dim DataProviderGroup As KDESolutions.Providers.DataProvider.DataProviderConfiguration = _
            CType(ConfigurationManager.GetSection(config("dataProviderGroup")), KDESolutions.Providers.DataProvider.DataProviderConfiguration)
            If String.IsNullOrEmpty(DataProviderGroup.DBType) = False Then
                _DBType = DataProviderGroup.DBType
                Me.MyDBType = DataProviderGroup.DBType
            End If
            If String.IsNullOrEmpty(DataProviderGroup.DBServer) = False Then
                _DBServer = DataProviderGroup.DBServer
            End If
            If String.IsNullOrEmpty(DataProviderGroup.DBUser) = False Then
                _DBUser = DataProviderGroup.DBUser
            End If
            If String.IsNullOrEmpty(DataProviderGroup.DBPassword) = False Then
                _DBPassword = DataProviderGroup.DBPassword
            End If
            If String.IsNullOrEmpty(DataProviderGroup.DBDatabaseName) = False Then
                _DBDatabaseName = DataProviderGroup.DBDatabaseName
            End If
            If String.IsNullOrEmpty(DataProviderGroup.ConnectString) = False Then
                _ConnectString = DataProviderGroup.ConnectString
            End If
        End If
        _AppId = GetAppId()

        If (Not (_PasswordStrengthRegularExpression) Is Nothing) Then
            _PasswordStrengthRegularExpression = _PasswordStrengthRegularExpression.Trim
            If (_PasswordStrengthRegularExpression.Length <> 0) Then
                Try
                    Dim regex As Regex = New Regex(_PasswordStrengthRegularExpression)
                Catch e As ArgumentException
                    Throw New ProviderException(e.Message, e)
                End Try
            End If
        Else
            _PasswordStrengthRegularExpression = String.Empty
        End If
        If (_MinRequiredNonalphanumericCharacters > _MinRequiredPasswordLength) Then
            Throw New HttpException("RequiredNonalphanumericCharacters can not be more than MinRequiredPasswordLength")
        End If
        _AppName = config("applicationName")
        If String.IsNullOrEmpty(_AppName) Then
            _AppName = GetDefaultAppName()
        End If
        If (_AppName.Length > 255) Then
            Throw New ProviderException("Provider application name too long")
        End If
        Dim strTemp As String = config("passwordFormat")
        If (strTemp Is Nothing) Then
            strTemp = "Hashed"
        End If
        Select Case (strTemp)
            Case "Clear"
                _PasswordFormat = MembershipPasswordFormat.Clear
            Case "Encrypted"
                _PasswordFormat = MembershipPasswordFormat.Encrypted
            Case "Hashed"
                _PasswordFormat = MembershipPasswordFormat.Hashed
            Case Else
                Throw New ProviderException("Provider bad password format")
        End Select
        If ((PasswordFormat = MembershipPasswordFormat.Hashed) _
                    AndAlso EnablePasswordRetrieval) Then
            Throw New ProviderException("Provider can not retrieve hashed password")
        End If
        config.Remove("description")
        config.Remove("enablePasswordRetrieval")
        config.Remove("enablePasswordReset")
        config.Remove("requiresQuestionAndAnswer")
        config.Remove("requiresUniqueEmail")
        config.Remove("maxInvalidPasswordAttempts")
        config.Remove("passwordAttemptWindow")
        config.Remove("minRequiredPasswordLength")
        config.Remove("minRequiredNonAlphanumericCharacters")
        config.Remove("passwordStrengthRegularExpression")
        config.Remove("tablePrefix")
        config.Remove("dataProviderGroup")
        config.Remove("applicationName")
        config.Remove("passwordFormat")
        config.Remove("productKey")
        config.Remove("activationDate")
        If (config.Count > 0) Then
            Dim attribUnrecognized As String = config.GetKey(0)
            If Not String.IsNullOrEmpty(attribUnrecognized) Then
                Throw New ProviderException("Provider unrecognized attribute " & attribUnrecognized)
            End If
        End If
    End Sub

    Public Overrides Function ChangePassword(ByVal username As String, ByVal oldPassword As String, ByVal newPassword As String) As Boolean
        CheckParameter(username, True, True, True, 255, "username")
        CheckParameter(oldPassword, True, True, False, 128, "oldPassword")
        CheckParameter(newPassword, True, True, False, 128, "newPassword")
        Dim salt As String = Nothing
        Dim passwordFormat As Integer
        Dim status As Integer
        If Not CheckPassword(username, oldPassword, False, False, salt, passwordFormat) Then
            Return False
        End If
        If (newPassword.Length < MinRequiredPasswordLength) Then
            Throw New ArgumentException("Password too short, " & MinRequiredPasswordLength.ToString(CultureInfo.InvariantCulture))
        End If
        Dim count As Integer = 0
        Dim i As Integer = 0
        Do While (i < newPassword.Length)
            If Not Char.IsLetterOrDigit(newPassword, i) Then
                count = (count + 1)
            End If
            i = (i + 1)
        Loop
        If (count < MinRequiredNonAlphanumericCharacters) Then
            Throw New ArgumentException("Password need more non alpha numeric chars," & MinRequiredNonAlphanumericCharacters.ToString(CultureInfo.InvariantCulture))
        End If
        If (PasswordStrengthRegularExpression.Length > 0) Then
            If Not Regex.IsMatch(newPassword, PasswordStrengthRegularExpression) Then
                Throw New ArgumentException("Password does not match regular expression")
            End If
        End If
        Dim pass As String = EncodePassword(newPassword, CType(passwordFormat, Integer), salt)
        If (pass.Length > 128) Then
            Throw New ArgumentException("Membership password too long")
        End If
        Dim e As ValidatePasswordEventArgs = New ValidatePasswordEventArgs(username, newPassword, False)
        OnValidatingPassword(e)
        If e.Cancel Then
            If (Not (e.FailureInformation) Is Nothing) Then
                Throw e.FailureInformation
            Else
                Throw New ArgumentException("Membership Custom Password Validation Failure")
            End If
        End If
        CheckSchemaVersion()
        status = Membership_SetPassword(Me.ApplicationName, username, pass, salt, DateTime.UtcNow, passwordFormat)
        If (status <> 0) Then
            Dim errText As String = GetExceptionText(status)
            If IsStatusDueToBadPassword(status) Then
                Throw New MembershipPasswordException(errText)
            Else
                Throw New ProviderException(errText)
            End If
        End If
        Return True
    End Function

    Public Overrides Function ChangePasswordQuestionAndAnswer(ByVal username As String, ByVal password As String, ByVal newPasswordQuestion As String, ByVal newPasswordAnswer As String) As Boolean
        CheckParameter(username, True, True, True, 255, "username")
        CheckParameter(password, True, True, False, 128, "password")
        Dim salt As String
        Dim passwordFormat As Integer
        If Not CheckPassword(username, password, False, False, salt, passwordFormat) Then
            Return False
        End If
        CheckParameter(newPasswordQuestion, RequiresQuestionAndAnswer, RequiresQuestionAndAnswer, False, 255, "newPasswordQuestion")
        Dim encodedPasswordAnswer As String
        If (Not (newPasswordAnswer) Is Nothing) Then
            newPasswordAnswer = newPasswordAnswer.Trim
        End If
        CheckParameter(newPasswordAnswer, RequiresQuestionAndAnswer, RequiresQuestionAndAnswer, False, 128, "newPasswordAnswer")
        If Not String.IsNullOrEmpty(newPasswordAnswer) Then
            encodedPasswordAnswer = EncodePassword(newPasswordAnswer.ToLower(CultureInfo.InvariantCulture), CType(passwordFormat, Integer), salt)
        Else
            encodedPasswordAnswer = newPasswordAnswer
        End If
        CheckParameter(encodedPasswordAnswer, RequiresQuestionAndAnswer, RequiresQuestionAndAnswer, False, 128, "newPasswordAnswer")
        Try
            CheckSchemaVersion()
            Dim status As Integer = Membership_ChangePasswordQuestionAndAnswer(ApplicationName, username, newPasswordQuestion, encodedPasswordAnswer)
            If (status <> 0) Then
                Throw New ProviderException(GetExceptionText(status))
            End If
            Return (status = 0)
        Catch Ex As System.Exception
            Throw
        End Try
    End Function

    Public Overrides Function CreateUser(ByVal username As String, ByVal password As String, ByVal email As String, ByVal passwordQuestion As String, ByVal passwordAnswer As String, ByVal isApproved As Boolean, ByVal providerUserKey As Object, ByRef status As System.Web.Security.MembershipCreateStatus) As System.Web.Security.MembershipUser
        If Not ValidateParameter(password, True, True, False, 128) Then
            status = MembershipCreateStatus.InvalidPassword
            Return Nothing
        End If
        Dim salt As String = GenerateSalt()
        Dim pass As String = EncodePassword(password, CType(_PasswordFormat, Integer), salt)
        If (pass.Length > 128) Then
            status = MembershipCreateStatus.InvalidPassword
            Return Nothing
        End If
        Dim encodedPasswordAnswer As String
        If (Not (passwordAnswer) Is Nothing) Then
            passwordAnswer = passwordAnswer.Trim
        End If
        If Not String.IsNullOrEmpty(passwordAnswer) Then
            If (passwordAnswer.Length > 128) Then
                status = MembershipCreateStatus.InvalidAnswer
                Return Nothing
            End If
            encodedPasswordAnswer = EncodePassword(passwordAnswer.ToLower(CultureInfo.InvariantCulture), CType(_PasswordFormat, Integer), salt)
        Else
            encodedPasswordAnswer = passwordAnswer
        End If
        If Not ValidateParameter(encodedPasswordAnswer, RequiresQuestionAndAnswer, True, False, 128) Then
            status = MembershipCreateStatus.InvalidAnswer
            Return Nothing
        End If
        If Not ValidateParameter(username, True, True, True, 255) Then
            status = MembershipCreateStatus.InvalidUserName
            Return Nothing
        End If
        If Not ValidateParameter(email, RequiresUniqueEmail, RequiresUniqueEmail, False, 255) Then
            status = MembershipCreateStatus.InvalidEmail
            Return Nothing
        End If
        If Not ValidateParameter(passwordQuestion, RequiresQuestionAndAnswer, True, False, 255) Then
            status = MembershipCreateStatus.InvalidQuestion
            Return Nothing
        End If
        If (Not (providerUserKey) Is Nothing) Then
            If Not (TypeOf providerUserKey Is Guid) Then
                status = MembershipCreateStatus.InvalidProviderUserKey
                Return Nothing
            End If
        End If
        If (password.Length < MinRequiredPasswordLength) Then
            status = MembershipCreateStatus.InvalidPassword
            Return Nothing
        End If
        Dim count As Integer = 0
        Dim i As Integer = 0
        Do While (i < password.Length)
            If Not Char.IsLetterOrDigit(password, i) Then
                count = (count + 1)
            End If
            i = (i + 1)
        Loop
        If (count < MinRequiredNonAlphanumericCharacters) Then
            status = MembershipCreateStatus.InvalidPassword
            Return Nothing
        End If
        If (PasswordStrengthRegularExpression.Length > 0) Then
            If Not Regex.IsMatch(password, PasswordStrengthRegularExpression) Then
                status = MembershipCreateStatus.InvalidPassword
                Return Nothing
            End If
        End If
        Dim e As ValidatePasswordEventArgs = New ValidatePasswordEventArgs(username, password, True)
        OnValidatingPassword(e)
        If e.Cancel Then
            status = MembershipCreateStatus.InvalidPassword
            Return Nothing
        End If
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        Try
            Try
                CheckSchemaVersion()
                Dim dt As DateTime = RoundToSeconds(DateTime.UtcNow)
                Dim sSQL As String
                If String.IsNullOrEmpty(_AppId) Then
                    _AppId = GetAppId()
                End If
                Dim sNewUserId As String
                sSQL = "SELECT UserId FROM " & _TablePrefix & "_Users WHERE LoweredUserName = ? AND ApplicationId = ?"
                DAP.AddParameter("@LoweredUserName", username.ToLower, DbType.String)
                DAP.AddParameter("@ApplicationId", _AppId, DbType.String)
                sNewUserId = DAP.ExecuteScalar(sSQL)
                If String.IsNullOrEmpty(sNewUserId) Then
                    sNewUserId = System.Guid.NewGuid.ToString
                    sSQL = "INSERT INTO " & _TablePrefix & "_Users(ApplicationId, UserId, UserName, LoweredUserName, IsAnonymous, LastActivityDate) " & _
                    "VALUES(?, ?, ?, ?, ?, ?)"
                    DAP.AddParameter("@ApplicationId", _AppId, DbType.String)
                    DAP.AddParameter("@UserId", sNewUserId, DbType.String)
                    DAP.AddParameter("@UserName", username, DbType.String)
                    DAP.AddParameter("@LoweredUserName", username.ToLower, DbType.String)
                    DAP.AddParameter("@IsAnonymous", 0, DbType.Int64)
                    DAP.AddParameter("@LastActivityDate", dt, DbType.DateTime)
                    If DAP.ExecuteNonQuery(sSQL) <> 1 Then
                        status = MembershipCreateStatus.ProviderError
                    End If
                Else
                    status = MembershipCreateStatus.DuplicateUserName
                    Return Nothing
                End If

                'Next Check to see if the email already exists
                If Me.RequiresUniqueEmail = True Then
                    Dim countEmail As Integer = 0
                    sSQL = "SELECT COUNT(Email) As SqlCount " & _
                    "FROM " & _TablePrefix & "_Membership WHERE ApplicationId = ? " & _
                    "AND LoweredEmail = ?"
                    DAP.AddParameter("@ApplicationId", _AppId, DbType.String)
                    DAP.AddParameter("@LoweredEmail", email.ToLower, DbType.String)
                    countEmail = DAP.ExecuteScalar(sSQL)
                    If countEmail > 0 Then
                        status = MembershipCreateStatus.DuplicateEmail
                        Return Nothing
                    End If
                End If
                Dim iStatus As Integer = -1
                sSQL = "INSERT INTO " & _TablePrefix & "_Membership" & _
                "(ApplicationId,UserId,[Password],PasswordSalt,Email," & _
                "LoweredEmail,PasswordQuestion,PasswordAnswer,PasswordFormat," & _
                "IsApproved,IsLockedOut,CreateDate,LastLoginDate,LastPasswordChangedDate," & _
                "LastLockoutDate,FailedPasswordAttemptCount,FailedPasswordAttemptWindowStart," & _
                "FailedPasswordAnswerAttemptCount,FailedPasswordAnswerAttemptWindowStart) " & _
                "VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
                DAP.AddParameter("@ApplicationId", _AppId, DbType.String)
                DAP.AddParameter("@UserId", sNewUserId, DbType.String)
                DAP.AddParameter("@Password", pass, DbType.String)
                DAP.AddParameter("@PasswordSalt", salt, DbType.String)
                DAP.AddParameter("@Email", email, DbType.String)
                DAP.AddParameter("@LoweredEmail", email.ToLower, DbType.String)
                DAP.AddParameter("@PasswordQuestion", passwordQuestion, DbType.String)
                DAP.AddParameter("@PasswordAnswer", encodedPasswordAnswer, DbType.String)
                DAP.AddParameter("@PasswordFormat", CType(PasswordFormat, Integer), DbType.Int64)
                If isApproved Then
                    DAP.AddParameter("@IsApproved", 1, DbType.Int16)
                Else
                    DAP.AddParameter("@IsApproved", 0, DbType.Int16)
                End If
                DAP.AddParameter("@IsLockedOut", 0, DbType.Int16)
                DAP.AddParameter("@CreateDate", dt, DbType.DateTime)
                DAP.AddParameter("@LastLoginDate", dt, DbType.DateTime)
                DAP.AddParameter("@LastPasswordChangedDate", dt, DbType.DateTime)
                DAP.AddParameter("@LastLockoutDate", New DateTime(1754, 1, 1, 0, 0, 0), DbType.DateTime)
                DAP.AddParameter("@FailedPasswordAttemptCount", 0, DbType.Int64)
                DAP.AddParameter("@FailedPasswordAttemptWindowStart", New DateTime(1754, 1, 1, 0, 0, 0), DbType.DateTime)
                DAP.AddParameter("@FailedPasswordAnswerAttemptCount", 0, DbType.Int64)
                DAP.AddParameter("@FailedPasswordAnswerAttemptWindowStart", New DateTime(1754, 1, 1, 0, 0, 0), DbType.DateTime)
                Dim result As Integer = DAP.ExecuteNonQuery(sSQL)
                If result <> 1 Then
                    iStatus = -1
                Else
                    iStatus = 0
                End If
                If ((iStatus < 0) _
                            OrElse (iStatus > CType(MembershipCreateStatus.ProviderError, Integer))) Then
                    iStatus = CType(MembershipCreateStatus.ProviderError, Integer)
                End If
                status = CType(iStatus, MembershipCreateStatus)
                If (iStatus <> 0) Then
                    Return Nothing
                End If
                providerUserKey = sNewUserId
                dt = dt.ToLocalTime
                Return New MembershipUser(Me.Name, username, providerUserKey, email, passwordQuestion, Nothing, isApproved, False, dt, dt, dt, dt, New DateTime(1754, 1, 1))
            Finally
                DAP.Dispose()
            End Try
        Catch Ex As System.Exception
            Throw
        End Try
    End Function

    Public Overrides Function DeleteUser(ByVal username As String, ByVal deleteAllRelatedData As Boolean) As Boolean
        CheckParameter(username, True, True, True, 255, "username")
        Try
            Dim status As Integer = Users_DeleteUser(ApplicationName, username, deleteAllRelatedData)
            Return (status > 0)
        Catch Ex As System.Exception
            Throw
        End Try
    End Function

    Public Overrides Function FindUsersByEmail(ByVal emailToMatch As String, ByVal pageIndex As Integer, ByVal pageSize As Integer, ByRef totalRecords As Integer) As System.Web.Security.MembershipUserCollection
        Try
            CheckParameter(emailToMatch, False, False, False, 255, "emailToMatch")
            If (pageIndex < 0) Then
                Throw New ArgumentException("PageIndex bad " & pageIndex)
            End If
            If (pageSize < 1) Then
                Throw New ArgumentException("PageSize bad " & pageSize)
            End If
            Dim upperBound As Long = ((CType(pageIndex, Long) * pageSize) _
                        + (pageSize - 1))
            If (upperBound > Int32.MaxValue) Then
                Throw New ArgumentException("PageIndex " & pageIndex & "PageSize " & pageSize & "bad")
            End If
            totalRecords = 0
            CheckSchemaVersion()
            Dim users As MembershipUserCollection = Membership_FindUsersByEmail(emailToMatch, pageIndex, pageSize)
            Return users
            totalRecords = users.Count
        Catch Ex As System.Exception
            Throw
        End Try
    End Function

    Public Overrides Function FindUsersByName(ByVal usernameToMatch As String, ByVal pageIndex As Integer, ByVal pageSize As Integer, ByRef totalRecords As Integer) As System.Web.Security.MembershipUserCollection
        CheckParameter(usernameToMatch, True, True, False, 255, "usernameToMatch")
        Try
            If (pageIndex < 0) Then
                Throw New ArgumentException("PageIndex " & pageIndex.ToString & " bad")
            End If
            If (pageSize < 1) Then
                Throw New ArgumentException("PageSize " & pageSize & " bad")
            End If
            Dim upperBound As Long = ((CType(pageIndex, Long) * pageSize) _
                        + (pageSize - 1))
            If (upperBound > Int32.MaxValue) Then
                Throw New ArgumentException("PageIndex " & pageIndex.ToString & " PageSize " & pageSize & " bad")
            End If
            totalRecords = 0
            CheckSchemaVersion()
            Dim users As MembershipUserCollection = Membership_FindUsersByName(usernameToMatch, pageIndex, pageSize)
            totalRecords = users.Count
            Return users
        Catch Ex As System.Exception
            Throw
        End Try
    End Function

    Public Overrides Function GetAllUsers(ByVal pageIndex As Integer, ByVal pageSize As Integer, ByRef totalRecords As Integer) As System.Web.Security.MembershipUserCollection
        If (pageIndex < 0) Then
            Throw New ArgumentException("PageIndex " & pageIndex.ToString & "bad")
        End If
        If (pageSize < 1) Then
            Throw New ArgumentException("PageSize " & pageSize.ToString & "bad")
        End If
        Dim upperBound As Long = ((CType(pageIndex, Long) * pageSize) _
                    + (pageSize - 1))
        If (upperBound > Int32.MaxValue) Then
            Throw New ArgumentException("PageIndex " & pageIndex & " PageSize " & pageSize & " bad")
        End If
        Dim users As MembershipUserCollection = New MembershipUserCollection
        totalRecords = users.Count
        CheckSchemaVersion()
        users = Membership_GetAllUsers(pageIndex, pageSize)
        Return users
    End Function

    Public Overrides Function GetNumberOfUsersOnline() As Integer
        CheckSchemaVersion()
        Return Membership_GetNumberOfUsersOnline(ApplicationName, System.Web.Security.Membership.UserIsOnlineTimeWindow, DateTime.UtcNow)
    End Function

    Public Overrides Function GetPassword(ByVal username As String, ByVal answer As String) As String
        If Not EnablePasswordRetrieval Then
            Throw New NotSupportedException("Membership Password Retrieval not supported")
        End If
        CheckParameter(username, True, True, True, 255, "username")
        Dim encodedPasswordAnswer As String = GetEncodedPasswordAnswer(username, answer)
        CheckParameter(encodedPasswordAnswer, RequiresQuestionAndAnswer, RequiresQuestionAndAnswer, False, 128, "passwordAnswer")
        Dim errText As String
        Dim passwordFormat As Integer = 0
        Dim status As Integer = 0
        Dim pass As String = GetPasswordFromDB(username, encodedPasswordAnswer, RequiresQuestionAndAnswer, passwordFormat, status)
        If (pass Is Nothing) Then
            errText = GetExceptionText(status)
            If IsStatusDueToBadPassword(status) Then
                Throw New MembershipPasswordException(errText)
            Else
                Throw New ProviderException(errText)
            End If
        End If
        Return UnEncodePassword(pass, passwordFormat)
    End Function

    Public Overloads Overrides Function GetUser(ByVal providerUserKey As Object, ByVal userIsOnline As Boolean) As System.Web.Security.MembershipUser
        If (providerUserKey Is Nothing) Then
            Throw New ArgumentNullException("providerUserKey")
        End If
        If Not (TypeOf providerUserKey Is Guid) Then
            Throw New ArgumentException("Membership Invalid Provider User Key " & providerUserKey.ToString)
        End If
        Try
            CheckSchemaVersion()
            Return Membership_GetUserByUserId(providerUserKey, DateTime.UtcNow, userIsOnline)
        Catch Ex As System.Exception
            Throw
        End Try
    End Function

    Public Overloads Overrides Function GetUser(ByVal username As String, ByVal userIsOnline As Boolean) As System.Web.Security.MembershipUser
        CheckParameter(username, True, False, True, 255, "username")
        Try
            CheckSchemaVersion()
            Dim result As Common.DbDataRecord
            result = Membership_GetUserByName(ApplicationName, username, DateTime.UtcNow, userIsOnline)
            If Not result Is Nothing Then
                Return GetUserFromReader(result)
            End If
            Return Nothing
        Catch Ex As System.Exception
            Throw
        End Try
    End Function



    Public Overrides Function GetUserNameByEmail(ByVal email As String) As String
        CheckParameter(email, False, False, False, 255, "email")
        CheckSchemaVersion()
        Return Membership_GetUserByEmail(ApplicationName, email)
    End Function

    Public Overrides Function ResetPassword(ByVal username As String, ByVal passwordAnswer As String) As String
        If Not EnablePasswordReset Then
            Throw New NotSupportedException("Not configured to support password resets")
        End If
        CheckParameter(username, True, True, True, 255, "username")
        Dim salt As String
        Dim passwordFormat As Integer
        Dim passwdFromDB As String
        Dim status As Integer
        Dim failedPasswordAttemptCount As Integer
        Dim failedPasswordAnswerAttemptCount As Integer
        Dim isApproved As Boolean
        Dim lastActivityDate As DateTime
        Dim lastLoginDate As DateTime
        GetPasswordWithFormat(username, False, status, passwdFromDB, passwordFormat, salt, failedPasswordAttemptCount, failedPasswordAnswerAttemptCount, isApproved, lastLoginDate, lastActivityDate)
        If (status <> 0) Then
            If IsStatusDueToBadPassword(status) Then
                Throw New MembershipPasswordException(GetExceptionText(status))
            Else
                Throw New ProviderException(GetExceptionText(status))
            End If
        End If
        Dim encodedPasswordAnswer As String
        If (Not (passwordAnswer) Is Nothing) Then
            passwordAnswer = passwordAnswer.Trim
        End If
        If Not String.IsNullOrEmpty(passwordAnswer) Then
            encodedPasswordAnswer = EncodePassword(passwordAnswer.ToLower(CultureInfo.InvariantCulture), passwordFormat, salt)
        Else
            encodedPasswordAnswer = passwordAnswer
        End If
        CheckParameter(encodedPasswordAnswer, RequiresQuestionAndAnswer, RequiresQuestionAndAnswer, False, 128, "passwordAnswer")
        Dim newPassword As String
        If Me.MinRequiredPasswordLength < 8 Then
            newPassword = System.Web.Security.Membership.GeneratePassword(8, Me.MinRequiredNonAlphanumericCharacters)
        Else
            newPassword = System.Web.Security.Membership.GeneratePassword(Me.MinRequiredPasswordLength, Me.MinRequiredNonAlphanumericCharacters)
        End If
        Dim e As ValidatePasswordEventArgs = New ValidatePasswordEventArgs(username, newPassword, False)
        OnValidatingPassword(e)
        If e.Cancel Then
            If (Not (e.FailureInformation) Is Nothing) Then
                Throw e.FailureInformation
            Else
                Throw New ProviderException("Membership Custom Password Validation Failure")
            End If
        End If
        Try
            status = Membership_ResetPassword(ApplicationName, username, EncodePassword(newPassword, CType(passwordFormat, Integer), salt), MaxInvalidPasswordAttempts, _
            PasswordAttemptWindow, salt, DateTime.UtcNow, CType(passwordFormat, Integer), encodedPasswordAnswer)
            Dim errText As String
            If (status <> 0) Then
                errText = GetExceptionText(status)
                If IsStatusDueToBadPassword(status) Then
                    Throw New MembershipPasswordException(errText)
                Else
                    Throw New ProviderException(errText)
                End If
            End If
            Return newPassword
        Catch Ex As System.Exception
            Throw
        End Try
    End Function

    Public Overrides Function UnlockUser(ByVal userName As String) As Boolean
        CheckParameter(userName, True, True, True, 255, "username")
        Try
            CheckSchemaVersion()
            Dim status As Integer = Membership_UnlockUser(ApplicationName, userName)
            If (status = 0) Then
                Return True
            End If
            Return False
        Catch Ex As System.Exception
            Throw
        End Try
    End Function

    Public Overrides Sub UpdateUser(ByVal user As System.Web.Security.MembershipUser)
        If (user Is Nothing) Then
            Throw New ArgumentNullException("user")
        End If
        Dim temp As String = user.UserName
        CheckParameter(temp, True, True, True, 255, "UserName")
        temp = user.Email
        CheckParameter(temp, RequiresUniqueEmail, RequiresUniqueEmail, False, 255, "Email")
        user.Email = temp
        Try
            CheckSchemaVersion()
            Dim status As Integer
            status = Membership_UpdateUser(ApplicationName, user.UserName, user.Email, user.Comment, user.IsApproved, _
            user.LastLoginDate.ToUniversalTime, user.LastActivityDate.ToUniversalTime, RequiresUniqueEmail, DateTime.UtcNow)
            If (status <> 0) Then
                Throw New ProviderException(GetExceptionText(status))
            End If
            Return
        Catch Ex As System.Exception
            Throw
        End Try
    End Sub

    Public Overrides Function ValidateUser(ByVal username As String, ByVal password As String) As Boolean
        If (ValidateParameter(username, True, True, True, 255) _
            AndAlso (ValidateParameter(password, True, True, False, 128) AndAlso CheckPassword(username, password, True, True))) Then
            ' Comment out perf counters in sample: PerfCounters.IncrementCounter(AppPerfCounter.MEMBER_SUCCESS);
            ' Comment out events in sample: WebBaseEvent.RaiseSystemEvent(null, WebEventCodes.AuditMembershipAuthenticationSuccess, username);
            Return True
        Else
            ' Comment out perf counters in sample: PerfCounters.IncrementCounter(AppPerfCounter.MEMBER_FAIL);
            ' Comment out events in sample: WebBaseEvent.RaiseSystemEvent(null, WebEventCodes.AuditMembershipAuthenticationFailure, username);
            Return False
        End If
    End Function

    Private Function GetEncodedPasswordAnswer(ByVal username As String, ByVal passwordAnswer As String) As String
        If (Not (passwordAnswer) Is Nothing) Then
            passwordAnswer = passwordAnswer.Trim
        End If
        If String.IsNullOrEmpty(passwordAnswer) Then
            Return passwordAnswer
        End If
        Dim failedPasswordAnswerAttemptCount As Integer
        Dim status As Integer
        Dim passwordFormat As Integer
        Dim failedPasswordAttemptCount As Integer
        Dim passwordSalt As String
        Dim password As String
        Dim isApproved As Boolean
        Dim lastActivityDate As DateTime
        Dim lastLoginDate As DateTime
        GetPasswordWithFormat(username, False, status, password, passwordFormat, passwordSalt, failedPasswordAttemptCount, failedPasswordAnswerAttemptCount, isApproved, lastLoginDate, lastActivityDate)
        If (status = 0) Then
            Return EncodePassword(passwordAnswer.ToLower(CultureInfo.InvariantCulture), passwordFormat, passwordSalt)
        Else
            Throw New ProviderException(GetExceptionText(status))
        End If
    End Function

    Private Function GetPasswordFromDB(ByVal username As String, ByVal passwordAnswer As String, ByVal requiresQuestionAndAnswer As Boolean, ByRef passwordFormat As Integer, ByRef status As Integer) As String
        CheckSchemaVersion()
        Dim result As String
        result = Membership_GetPassword(ApplicationName, username, MaxInvalidPasswordAttempts, PasswordAttemptWindow, DateTime.UtcNow, passwordAnswer)
        Dim password As String = Nothing
        If String.IsNullOrEmpty(result) = False AndAlso result.Contains(",") Then
            password = result.Split(",").GetValue(0)
            passwordFormat = result.Split(",").GetValue(1)
        Else
            password = Nothing
            passwordFormat = 0
        End If
        Return password
    End Function
#End Region

#Region "Private helper functions"
    ''' <summary>
    ''' The CheckSchemaVersion method queries the database to check if this is the correct version of the provider.
    ''' </summary>
    ''' <remarks>Each feature needs a row in the database to indicate that it is the current version of the given feature.</remarks>
    Private Sub CheckSchemaVersion()
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        Dim features(1) As String
        features(0) = "Common"
        features(1) = "Membership"

        Dim version As String = "1"
        If (features Is Nothing) Then
            Throw New ArgumentNullException("features")
        End If
        If (version Is Nothing) Then
            Throw New ArgumentNullException("version")
        End If
        Dim iStatus As Integer = 0
        For Each feature As String In features
            Dim sSQL As String = "SELECT COUNT(*) As SqlCount " & _
            "FROM " & _TablePrefix & "_SchemaVersions " & _
            "WHERE Feature = ? " & _
            "AND CompatibleSchemaVersion = ?"
            DAP.AddParameter("@Feature", feature, DbType.String)
            DAP.AddParameter("@CompatibleSchemaVersion", version, DbType.Int16)
            iStatus = DAP.ExecuteScalar(sSQL)
            If (iStatus = 0) Then
                _SchemaVersionCheck = -1
                Throw New ProviderException("Provider Schema Version Not Match " & MyBase.ToString & " version")
            End If
        Next
        If (_SchemaVersionCheck = -1) Then
            Throw New ProviderException("Provider Schema Version Not Match " & MyBase.ToString & " " & version)
        ElseIf (_SchemaVersionCheck = 0) Then
            If (_SchemaVersionCheck = -1) Then
                Throw New ProviderException("Provider Schema Version Not Match " & MyBase.ToString & " " & version)
            ElseIf (_SchemaVersionCheck = 0) Then
                _SchemaVersionCheck = 1
            End If
        End If
        DAP.Dispose()
    End Sub

    Private Function IsStatusDueToBadPassword(ByVal status As Integer) As Boolean
        Return (((status >= 2) _
                    AndAlso (status <= 6)) _
                    OrElse (status = 99))
    End Function

    Private Function RoundToSeconds(ByVal dt As DateTime) As DateTime
        Return New DateTime(dt.Year, dt.Month, dt.Day, dt.Hour, dt.Minute, dt.Second)
    End Function

    Friend Function GenerateSalt() As String
        Dim buf() As Byte = New Byte((16) - 1) {}
        Dim myCrypto As New RNGCryptoServiceProvider
        myCrypto.GetBytes(buf)
        Return Convert.ToBase64String(buf)
    End Function

    Friend Function EncodePassword(ByVal pass As String, ByVal passwordFormat As Integer, ByVal salt As String) As String
        If (passwordFormat = 0) Then
            Return pass
        End If
        Dim bIn() As Byte = Encoding.Unicode.GetBytes(pass)
        Dim bSalt() As Byte = Convert.FromBase64String(salt)
        Dim bAll() As Byte = New Byte(((bSalt.Length + bIn.Length)) - 1) {}
        Dim bRet() As Byte = Nothing
        Buffer.BlockCopy(bSalt, 0, bAll, 0, bSalt.Length)
        Buffer.BlockCopy(bIn, 0, bAll, bSalt.Length, bIn.Length)
        If (passwordFormat = 1) Then
            ' MembershipPasswordFormat.Hashed
            Dim s As HashAlgorithm = HashAlgorithm.Create(System.Web.Security.Membership.HashAlgorithmType)
            bRet = s.ComputeHash(bAll)
        Else
            bRet = EncryptPassword(bAll)
        End If
        Return Convert.ToBase64String(bRet)
    End Function

    Friend Function UnEncodePassword(ByVal pass As String, ByVal passwordFormat As Integer) As String
        Select Case (passwordFormat)
            Case 0
                ' MembershipPasswordFormat.Clear:
                Return pass
            Case 1
                ' MembershipPasswordFormat.Hashed:
                Throw New ProviderException("Provider can not decode hashed password")
            Case Else
                Dim bIn() As Byte = Convert.FromBase64String(pass)
                Dim bRet() As Byte = DecryptPassword(bIn)
                If (bRet Is Nothing) Then
                    Return Nothing
                End If
                Return Encoding.Unicode.GetString(bRet, 16, (bRet.Length - 16))
        End Select
    End Function

    Private Function GetConfigValue(ByVal configValue As String, ByVal defaultValue As String) As String
        If String.IsNullOrEmpty(configValue) Then
            Return defaultValue
        End If
        Return configValue
    End Function

    Private Shared Function GetDefaultAppName() As String
        Try
            Dim appName As String = System.Web.Hosting.HostingEnvironment.ApplicationVirtualPath
            If String.IsNullOrEmpty(appName) Then
                appName = System.Diagnostics.Process.GetCurrentProcess.MainModule.ModuleName
                Dim indexOfDot As Integer = appName.IndexOf(ChrW(46))
                If (indexOfDot <> -1) Then
                    appName = appName.Remove(indexOfDot)
                End If
            End If
            If String.IsNullOrEmpty(appName) Then
                Return "/"
            Else
                Return appName
            End If
        Catch Ex As System.Exception
            Return "/"
        End Try
    End Function

    Friend Shared Function ValidateParameter(ByRef param As String, ByVal checkForNull As Boolean, ByVal checkIfEmpty As Boolean, ByVal checkForCommas As Boolean, ByVal maxSize As Integer) As Boolean
        If (param Is Nothing) Then
            Return Not checkForNull
        End If
        param = param.Trim
        If ((checkIfEmpty _
                    AndAlso (param.Length < 1)) _
                    OrElse (((maxSize > 0) _
                    AndAlso (param.Length > maxSize)) _
                    OrElse (checkForCommas AndAlso param.Contains(",")))) Then
            Return False
        End If
        Return True
    End Function

    Private Function GetExceptionText(ByVal status As Integer) As String
        Select Case (status)
            Case 0
                Return String.Empty
            Case 1
                Return "User Not Found"
            Case 2
                Return "Wrong Password"
            Case 3
                Return "Wrong Answer"
            Case 4
                Return "Invalid Password"
            Case 5
                Return "Invalid Question"
            Case 6
                Return "Invalid Answer"
            Case 7
                Return "Invalid Email"
            Case 99
                Return "Account Lock Out"
            Case Else
                Return "Error"
        End Select
    End Function

    Friend Shared Sub CheckParameter(ByRef param As String, ByVal checkForNull As Boolean, ByVal checkIfEmpty As Boolean, ByVal checkForCommas As Boolean, ByVal maxSize As Integer, ByVal paramName As String)
        If (param Is Nothing) Then
            If checkForNull Then
                Throw New ArgumentNullException(paramName)
            End If
            Return
        End If
        param = param.Trim
        If (checkIfEmpty _
                    AndAlso (param.Length < 1)) Then
            Throw New ArgumentException("Parameter can not be empty, " & paramName)
        End If
        If ((maxSize > 0) _
                    AndAlso (param.Length > maxSize)) Then
            Throw New ArgumentException("Parameter too long " & paramName & " " & maxSize.ToString(CultureInfo.InvariantCulture) & " " & paramName)
        End If
        If (checkForCommas AndAlso param.Contains(",")) Then
            Throw New ArgumentException("Parameter can not contain comma, " & paramName)
        End If
    End Sub

    Private Overloads Function CheckPassword(ByVal username As String, ByVal password As String, ByVal updateLastLoginActivityDate As Boolean, ByVal failIfNotApproved As Boolean) As Boolean
        Dim salt As String
        Dim passwordFormat As Integer
        Return CheckPassword(username, password, updateLastLoginActivityDate, failIfNotApproved, salt, passwordFormat)
    End Function

    Private Overloads Function CheckPassword(ByVal username As String, ByVal password As String, ByVal updateLastLoginActivityDate As Boolean, ByVal failIfNotApproved As Boolean, ByRef salt As String, ByRef passwordFormat As Integer) As Boolean
        Dim returnVal As Boolean = False
        Dim passwdFromDB As String
        Dim status As Integer
        Dim failedPasswordAttemptCount As Integer
        Dim failedPasswordAnswerAttemptCount As Integer
        Dim isPasswordCorrect As Boolean
        Dim isApproved As Boolean
        Dim lastActivityDate As DateTime
        Dim lastLoginDate As DateTime
        GetPasswordWithFormat(username, updateLastLoginActivityDate, status, passwdFromDB, passwordFormat, salt, failedPasswordAttemptCount, failedPasswordAnswerAttemptCount, isApproved, lastLoginDate, lastActivityDate)
        If (isApproved = False AndAlso failIfNotApproved) Then
            Return False
        End If
        Dim encodedPasswd As String = EncodePassword(password, passwordFormat, salt)
        isPasswordCorrect = passwdFromDB.Equals(encodedPasswd)
        If (isPasswordCorrect _
                    AndAlso ((failedPasswordAttemptCount = 0) _
                    AndAlso (failedPasswordAnswerAttemptCount = 0))) Then
            returnVal = True
        End If
        CheckSchemaVersion()
        status = Membership_UpdateUserInfo(username, isPasswordCorrect, updateLastLoginActivityDate, MaxInvalidPasswordAttempts, _
        PasswordAttemptWindow, DateTime.UtcNow, lastLoginDate, lastActivityDate)
        If (status <> 0) Then
            returnVal = False
        End If
        Return returnVal
    End Function

    Private Sub GetPasswordWithFormat(ByVal username As String, ByVal updateLastLoginActivityDate As Boolean, _
    ByRef status As Integer, ByRef password As String, ByRef passwordFormat As Integer, _
    ByRef passwordSalt As String, ByRef failedPasswordAttemptCount As Integer, _
    ByRef failedPasswordAnswerAttemptCount As Integer, ByRef isApproved As Boolean, _
    ByRef lastLoginDate As DateTime, ByRef lastActivityDate As DateTime)
        Try
            CheckSchemaVersion()
            Dim result As Common.DbDataRecord = Membership_GetPasswordWithFormat(username, updateLastLoginActivityDate, DateTime.UtcNow)
            status = -1
            If Not result Is Nothing Then
                password = result.GetString(2)
                passwordFormat = result.GetValue(3)
                passwordSalt = result.GetString(4)
                failedPasswordAttemptCount = result.GetValue(5)
                failedPasswordAnswerAttemptCount = result.GetValue(6)
                isApproved = result.GetBoolean(7)
                lastLoginDate = result.GetDateTime(8)
                lastActivityDate = result.GetDateTime(9)
                status = 0
            Else
                password = Nothing
                passwordFormat = 0
                passwordSalt = Nothing
                failedPasswordAttemptCount = 0
                failedPasswordAnswerAttemptCount = 0
                isApproved = False
                lastLoginDate = DateTime.UtcNow
                lastActivityDate = DateTime.UtcNow
            End If
        Catch Ex As System.Exception
            Throw
        End Try
    End Sub

    Private Function GetAppId() As String
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        'Check to see if the application exists
        Dim sSQL As String = "SELECT  ApplicationId FROM " & _TablePrefix & "_Applications WHERE LoweredApplicationName = ?"
        DAP.AddParameter("@ApplicationName", _ApplicationName, DbType.String)
        _AppId = DAP.ExecuteScalar(sSQL)
        'If it doesn't insert a new application into the _Applications table

        If String.IsNullOrEmpty(_AppId) Then
            Dim sNewAppId As String = System.Guid.NewGuid.ToString
            sSQL = "INSERT INTO " & _TablePrefix & "_Applications(ApplicationId, ApplicationName, LoweredApplicationName) " & _
            "VALUES(?, ?, ?)"
            DAP.AddParameter("@ApplicationId", sNewAppId, DbType.String)
            DAP.AddParameter("@ApplicationName", _ApplicationName, DbType.String)
            DAP.AddParameter("@LoweredApplicationName", _ApplicationName.ToLower, DbType.String)
            If DAP.ExecuteNonQuery(sSQL) = 1 Then
                _AppId = sNewAppId
            Else
                _AppId = Nothing
            End If
        End If
        Return _AppId
    End Function

    Public Function GetUserFromReader(ByVal reader As Common.DbDataRecord) As MembershipUser
        Dim providerUserKey As Object = reader.Item("UserId")
        Dim username As String = reader.Item("UserName")
        Dim email As String = reader.Item("Email")

        Dim passwordQuestion As String = ""
        If Not reader.Item("PasswordQuestion") Is DBNull.Value Then _
          passwordQuestion = reader.Item("PasswordQuestion")

        Dim comment As String = ""
        If Not reader.Item("Comment") Is DBNull.Value Then _
          comment = reader.Item("Comment")

        Dim isApproved As Boolean = reader.Item("IsApproved")
        Dim isLockedOut As Boolean = reader.Item("IsLockedOut")
        Dim creationDate As DateTime = reader.Item("CreateDate")

        Dim lastLoginDate As DateTime = New DateTime()
        If Not reader.Item("LastLoginDate") Is DBNull.Value Then _
          lastLoginDate = reader.Item("LastLoginDate")

        Dim lastActivityDate As DateTime = reader.Item("LastActivityDate")
        Dim lastPasswordChangedDate As DateTime = reader.Item("LastPasswordChangedDate")

        Dim lastLockedOutDate As DateTime = New DateTime()
        If Not reader.Item("LastLockoutDate") Is DBNull.Value Then _
          lastLockedOutDate = reader.Item("LastLockoutDate")

        Dim u As MembershipUser = New MembershipUser(Me.Name, _
                                              username, _
                                              providerUserKey, _
                                              email, _
                                              passwordQuestion, _
                                              comment, _
                                              isApproved, _
                                              isLockedOut, _
                                              creationDate, _
                                              lastLoginDate, _
                                              lastActivityDate, _
                                              lastPasswordChangedDate, _
                                              lastLockedOutDate)

        Return u
    End Function

#End Region

#Region "Converted Procedures"
    Private Function Membership_SetPassword(ByVal ApplicationName As String, _
    ByVal Username As String, ByVal NewPassword As String, ByVal PasswordSalt As String, _
    ByVal CurrentTimeUtc As DateTime, ByVal PasswordFormat As Integer) As Integer
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        Try
            Dim sUserId As String
            Dim sSQL As String
            sSQL = "SELECT u.UserId " & _
                "FROM " & _TablePrefix & "_Users u, " & _TablePrefix & "_Applications a, " & _
                _TablePrefix & "_Membership m " & _
                "WHERE LoweredUserName = ? AND " & _
                "u.ApplicationId = a.ApplicationId AND " & _
                "a.LoweredApplicationName = ? " & _
                "AND u.UserId = m.UserId"
            DAP.AddParameter("@LoweredUserName", Username.ToLower, DbType.String)
            DAP.AddParameter("@LoweredApplicationName", Me.ApplicationName.ToLower, DbType.String)
            sUserId = DAP.ExecuteScalar(sSQL)
            If String.IsNullOrEmpty(sUserId) Then
                Return 1
            Else
                sSQL = "UPDATE " & _TablePrefix & "_Membership " & _
                "SET [Password] = ?, PasswordFormat = ?, PasswordSalt = ?, " & _
                "LastPasswordChangedDate = ? " & _
                "WHERE UserId = ?"
                DAP.AddParameter("@Password", NewPassword, DbType.String)
                DAP.AddParameter("@PasswordFormat", PasswordFormat, DbType.Int64)
                DAP.AddParameter("@PasswordSalt", PasswordSalt, DbType.String)
                DAP.AddParameter("@LastPasswordChangedDate", CurrentTimeUtc, DbType.DateTime)
                DAP.AddParameter("@UserId", sUserId, DbType.String)
                If DAP.ExecuteNonQuery(sSQL) = 1 Then
                    Return 0
                Else
                    Return 1
                End If
            End If
        Finally
            DAP.Dispose()
        End Try
    End Function

    Private Function Membership_ChangePasswordQuestionAndAnswer(ByVal ApplicationName As String, _
    ByVal Username As String, ByVal NewPasswordQuestion As String, ByVal NewPasswordAnswer As String) As Integer
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        Try
            Dim sUserId As String
            Dim sSQL As String
            sSQL = "SELECT u.UserId " & _
            "FROM " & _TablePrefix & "_Membership m, " & _
            _TablePrefix & "_Users u, " & _TablePrefix & "_Applications a " & _
            "WHERE LoweredUserName = ? " & _
            "AND a.ApplicationId = ? " & _
            "AND a.LoweredApplicationName = ? " & _
            "AND m.UserId = ?"
            sUserId = DAP.ExecuteScalar(sSQL)
            If String.IsNullOrEmpty(sUserId) Then
                Return 1
            Else
                sSQL = "UPDATE " & _TablePrefix & "_Membership " & _
                "SET PasswordQuestion = ?, PasswordAnswer = ? " & _
                "WHERE UserId = ? "
                DAP.AddParameter("@PasswordQuestion", NewPasswordQuestion, DbType.String)
                DAP.AddParameter("@PasswordAnswer", NewPasswordAnswer, DbType.String)
                DAP.AddParameter("@UserId", sUserId, DbType.String)
                If DAP.ExecuteNonQuery(sSQL) = 1 Then
                    Return 0
                Else
                    Return 1
                End If
            End If
        Finally
            DAP.Dispose()
        End Try
    End Function

    Private Function Users_DeleteUser(ByVal ApplicationName As String, _
    ByVal UserName As String, ByVal DeleteRelatedData As Boolean) As Integer
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        Try
            Dim sUserId As String
            Dim iReturnVal As Integer = 0
            Dim sSQL As String
            sSQL = "SELECT UserId " & _
            "FROM " & _TablePrefix & "_Users " & _
            "WHERE LoweredUserName = ? " & _
            "AND ApplicationId = ? "
            DAP.AddParameter("@LoweredUserName", UserName.ToLower, DbType.String)
            DAP.AddParameter("@ApplicationId", _AppId, DbType.String)
            sUserId = DAP.ExecuteScalar(sSQL)
            If String.IsNullOrEmpty(sUserId) Then
                Return 0
            End If
            'Delete from the Membership table if it exists
            If (DeleteRelatedData = True) AndAlso DAP.TableExists(_TablePrefix & "_Membership") Then
                sSQL = "DELETE FROM " & _TablePrefix & "_Membership WHERE UserId = ?"
                DAP.AddParameter("@UserId", sUserId, DbType.String)
                If DAP.ExecuteNonQuery(sSQL) = 0 Then
                    iReturnVal = 0
                Else
                    iReturnVal = 1
                End If
            End If
            'Delete from the UsersInRoles tables if it exists
            If (DeleteRelatedData = True) AndAlso DAP.TableExists(_TablePrefix & "_UsersInRoles") Then
                sSQL = "DELETE FROM " & _TablePrefix & "_UsersInRoles WHERE UserId = ?"
                DAP.AddParameter("@UserId", sUserId, DbType.String)
                DAP.ExecuteNonQuery(sSQL)
            End If
            'Delete from the Profile table if it exist
            If (DeleteRelatedData = True) AndAlso DAP.TableExists(_TablePrefix & "_Profile") Then
                sSQL = "DELETE FROM " & _TablePrefix & "_Profile WHERE UserId = ?"
                DAP.AddParameter("@UserId", sUserId, DbType.String)
                DAP.ExecuteNonQuery(sSQL)
            End If
            'Delete from the PersonalizationPerUser table if it exists
            If (DeleteRelatedData = True) AndAlso DAP.TableExists(_TablePrefix & "_PersonalizationPerUser") Then
                sSQL = "DELETE FROM " & _TablePrefix & "_PersonalizationPerUser WHERE UserId = ?"
                DAP.AddParameter("@UserId", sUserId, DbType.String)
                DAP.ExecuteNonQuery(sSQL)
            End If
            'Delete from the Users table if it exists
            If (DeleteRelatedData = True) AndAlso DAP.TableExists(_TablePrefix & "_Users") Then
                sSQL = "DELETE FROM " & _TablePrefix & "_Users WHERE UserId = ?"
                DAP.AddParameter("@UserId", sUserId, DbType.String)
                If DAP.ExecuteNonQuery(sSQL) = 0 Then
                    iReturnVal = 0
                Else
                    iReturnVal = 1
                End If
            End If
            Return iReturnVal
        Finally
            DAP.Dispose()
        End Try
    End Function

    Private Function Membership_FindUsersByEmail(ByVal EmailToMatch As String, _
        ByVal PageIndex As Integer, ByVal PageSize As Integer) As System.Web.Security.MembershipUserCollection
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        Try
            Dim PageLowerBound As Integer
            Dim PageUpperBound As Integer
            PageLowerBound = (PageSize * PageIndex)
            PageUpperBound = ((PageSize - 1) + PageLowerBound)

            Dim sSQL As String
            sSQL = "SELECT  u.UserId u.UserName, m.Email, m.PasswordQuestion, m.Comment, m.IsApproved, " & _
                "m.CreateDate, m.LastLoginDate, u.LastActivityDate, m.LastPasswordChangedDate, " & _
                "u.UserId, m.IsLockedOut, m.LastLockoutDate " & _
                "FROM " & _TablePrefix & "_Membership m, " & _TablePrefix & "_Users u"
            If String.IsNullOrEmpty(EmailToMatch) Then
                sSQL = sSQL & "WHERE m.LoweredEmail IS NULL "
            Else
                sSQL = sSQL & "WHERE m.LoweredEmail Like ? "
            End If
            sSQL = sSQL & "AND m.UserId = u.UserId " & _
                          "AND u.ApplicationId = ? " & _
                          "ORDER BY m.LoweredEmail"
            DAP.AddParameter("@LoweredEmail", EmailToMatch.ToLower, DbType.String)
            DAP.AddParameter("@ApplicationId", _AppId, DbType.String)
            Dim myRecords As New Generic.List(Of Common.DbDataRecord)
            myRecords = DAP.ExecuteReader(sSQL)
            Dim returnCollection As New System.Web.Security.MembershipUserCollection
            Dim i As Integer = 0
            For Each recordItem As Common.DbDataRecord In myRecords
                If i >= PageLowerBound Then
                    Dim u As MembershipUser = GetUserFromReader(recordItem)
                    returnCollection.Add(u)
                End If
                If i >= PageUpperBound Then
                    Exit For
                End If
                i += 1
            Next
            Return returnCollection
        Finally
            DAP.Dispose()
        End Try
    End Function

    Private Function Membership_FindUsersByName(ByVal UserNameToMatch As String, ByVal PageIndex As Integer, ByVal PageSize As Integer) As System.Web.Security.MembershipUserCollection
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        Try
            Dim PageLowerBound As Integer
            Dim PageUpperBound As Integer
            PageLowerBound = (PageSize * PageIndex)
            PageUpperBound = ((PageSize - 1) + PageLowerBound)

            Dim sSQL As String
            sSQL = "SELECT  u.UserName, m.Email, m.PasswordQuestion, m.Comment, m.IsApproved, " & _
                "m.CreateDate, m.LastLoginDate, u.LastActivityDate, m.LastPasswordChangedDate, " & _
                "u.UserId, m.IsLockedOut, m.LastLockoutDate " & _
                "FROM " & _TablePrefix & "_Membership m, " & _TablePrefix & "_Users u "
            If String.IsNullOrEmpty(UserNameToMatch) Then
                sSQL = sSQL & "WHERE u.LoweredUserName IS NULL "
            Else
                sSQL = sSQL & "WHERE u.LoweredUserName Like ? "
            End If
            sSQL = sSQL & "AND m.UserId = u.UserId " & _
                          "AND u.ApplicationId = ? " & _
                          "ORDER BY u.Username"
            If String.IsNullOrEmpty(UserNameToMatch) = False Then
                DAP.AddParameter("@LoweredUserName", "%" & UserNameToMatch.ToLower & "%", DbType.String)
            End If
            DAP.AddParameter("@ApplicationId", _AppId, DbType.String)
            Dim myRecords As New Generic.List(Of Common.DbDataRecord)
            myRecords = DAP.ExecuteReader(sSQL)
            Dim returnCollection As New System.Web.Security.MembershipUserCollection
            Dim i As Integer = 0
            For Each recordItem As Common.DbDataRecord In myRecords
                If i >= PageLowerBound Then
                    Dim u As MembershipUser = GetUserFromReader(recordItem)
                    returnCollection.Add(u)
                End If
                If i >= PageUpperBound Then
                    Exit For
                End If
                i += 1
            Next
            Return returnCollection
        Finally
            DAP.Dispose()
        End Try
    End Function

    Private Function Membership_GetAllUsers(ByVal PageIndex As Integer, ByVal PageSize As Integer) As System.Web.Security.MembershipUserCollection
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        Try
            Dim PageLowerBound As Integer
            Dim PageUpperBound As Integer
            PageLowerBound = (PageSize * PageIndex)
            PageUpperBound = ((PageSize - 1) + PageLowerBound)

            Dim sSQL As String
            sSQL = "SELECT u.UserId, u.UserName, m.Email, m.PasswordQuestion, m.Comment, m.IsApproved, " & _
                "m.CreateDate, m.LastLoginDate, u.LastActivityDate, m.LastPasswordChangedDate, " & _
                "u.UserId, m.IsLockedOut, m.LastLockoutDate " & _
                "FROM " & _TablePrefix & "_Membership m, " & _TablePrefix & "_Users u " & _
                "WHERE m.UserId = u.UserId " & _
                "AND u.ApplicationId = ? " & _
                "ORDER BY u.Username"
            DAP.AddParameter("@ApplicationId", _AppId, DbType.String)
            Dim myRecords As New Generic.List(Of Common.DbDataRecord)
            myRecords = DAP.ExecuteReader(sSQL)
            Dim returnCollection As New System.Web.Security.MembershipUserCollection
            Dim i As Integer = 0
            For Each recordItem As Common.DbDataRecord In myRecords
                If i >= PageLowerBound Then
                    Dim u As MembershipUser = GetUserFromReader(recordItem)
                    returnCollection.Add(u)
                End If
                If i >= PageUpperBound Then
                    Exit For
                End If
                i += 1
            Next
            Return returnCollection
        Finally
            DAP.Dispose()
        End Try

    End Function

    Private Function Membership_GetNumberOfUsersOnline(ByVal ApplicationName As String, ByVal minutesSinceLastActive As Double, ByVal CurrentTimeUtc As DateTime) As Integer
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        Try
            Dim DateActive As DateTime
            Dim result As Integer = 0
            DateActive = CurrentTimeUtc.AddMinutes((minutesSinceLastActive * -1))
            Dim sSQL As String = "SELECT COUNT(*) As NumOnline " & _
                    "FROM " & _TablePrefix & "_Users u, " & _
                _TablePrefix & "_Applications a, " & _
                _TablePrefix & "_Membership m " & _
                "WHERE u.ApplicationId = a.ApplicationId " & _
                "AND LastActivityDate > ? " & _
                "AND a.LoweredApplicationName = ? " & _
                "AND u.UserId = m.UserId"
            DAP.AddParameter("@LastActivityDate", DateActive, DbType.DateTime)
            DAP.AddParameter("@LoweredApplicationName", ApplicationName.ToLower, DbType.String)
            result = DAP.ExecuteScalar(sSQL)
            Return result
        Finally
            DAP.Dispose()
        End Try
    End Function

    Private Function Membership_GetPassword(ByVal ApplicationName As String, ByVal UserName As String, _
    ByVal MaxInvalidPasswordAttempts As Integer, ByVal PasswordAttemptWindow As Integer, _
    ByVal CurrentTimeUtc As DateTime, ByVal PasswordAnswer As String) As String
        Dim errorCode As Integer = 0
        Dim UserId As String = Nothing
        Dim PasswordFormat As Integer
        Dim Password As String
        Dim passAns As String
        Dim IsLockedOut As Boolean
        Dim LastLockoutDate As DateTime
        Dim FailedPasswordAttemptCount As Integer
        Dim FailedPasswordAttemptWindowStart As DateTime
        Dim FailedPasswordAnswerAttemptCount As Integer
        Dim FailedPasswordAnswerAttemptWindowStart As DateTime
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        Try
            Dim sSQL As String = "    SELECT u.UserId, " & _
                "m.Password, m.PasswordAnswer, " & _
                "PasswordFormat, m.IsLockedOut, " & _
                "m.LastLockoutDate, m.FailedPasswordAttemptCount, " & _
                "m.FailedPasswordAttemptWindowStart, " & _
                "m.FailedPasswordAnswerAttemptCount, " & _
                "m.FailedPasswordAnswerAttemptWindowStart " & _
                "FROM " & _TablePrefix & "_Applications a, " & _
                _TablePrefix & "_Users u, " & _TablePrefix & "_Membership m " & _
                "WHERE a.LoweredApplicationName = ? " & _
                "AND u.ApplicationId = a.ApplicationId " & _
                "AND u.UserId = m.UserId AND " & _
                "u.LoweredUserName = ?"
            DAP.AddParameter("@LoweredApplicationName", ApplicationName.ToLower, DbType.String)
            DAP.AddParameter("@LoweredUserName", UserName.ToLower, DbType.String)
            Dim result As New Generic.List(Of Common.DbDataRecord)
            result = DAP.ExecuteReader(sSQL)
            If result.Count = 0 Or result.Count > 1 Then
                errorCode = 1
            Else
                UserId = result.Item(0).GetString("UserId")
                PasswordFormat = result.Item(0).GetInt64("PasswordFormat")
                Password = result.Item(0).GetString("Password")
                passAns = result.Item(0).GetString("PasswordAnswer")
                If result.Item(0).GetInt64("IsLockedOut") = 0 Then
                    IsLockedOut = False
                Else
                    IsLockedOut = True
                End If
                LastLockoutDate = result.Item(0).GetDateTime("LastLockoutDate")
                FailedPasswordAttemptCount = result.Item(0).GetInt64("FailedPasswordAttemptCount")
                FailedPasswordAttemptWindowStart = result.Item(0).GetDateTime("FailedPasswordAttemptWindowStart")
                FailedPasswordAnswerAttemptCount = result.Item(0).GetInt64("FailedPasswordAnswerAttemptCount")
                FailedPasswordAnswerAttemptWindowStart = result.Item(0).GetDateTime("FailedPasswordAnswerAttemptWindowStart")
                If IsLockedOut Then
                    errorCode = 99
                End If
                If String.IsNullOrEmpty(PasswordAnswer) = False Then
                    If String.IsNullOrEmpty(passAns) Or (passAns.ToLower <> PasswordAnswer.ToLower) Then
                        If CurrentTimeUtc > (FailedPasswordAnswerAttemptWindowStart.AddMinutes(PasswordAttemptWindow)) Then
                            FailedPasswordAnswerAttemptWindowStart = CurrentTimeUtc
                            FailedPasswordAnswerAttemptCount = 1
                        Else
                            FailedPasswordAnswerAttemptCount = (FailedPasswordAnswerAttemptCount + 1)
                            FailedPasswordAnswerAttemptWindowStart = CurrentTimeUtc
                        End If

                        If (FailedPasswordAnswerAttemptCount >= MaxInvalidPasswordAttempts) Then
                            IsLockedOut = 1
                            LastLockoutDate = CurrentTimeUtc
                        End If
                        errorCode = 3
                    Else
                        If FailedPasswordAnswerAttemptCount > 0 Then
                            FailedPasswordAnswerAttemptCount = 0
                            FailedPasswordAnswerAttemptWindowStart = #1/1/1754#
                        End If

                        sSQL = "UPDATE " & _TablePrefix & "_Membership " & _
                        "SET IsLockedOut = ?, LastLockoutDate = ?, " & _
                        "FailedPasswordAttemptCount = ?, FailedPasswordAttemptWindowStart = ?, " & _
                        "FailedPasswordAnswerAttemptCount = ?, FailedPasswordAnswerAttemptWindowStart = ? " & _
                        "WHERE UserId = ?"
                        If IsLockedOut Then
                            DAP.AddParameter("@IsLockedOut", 1, DbType.Int16)
                        Else
                            DAP.AddParameter("@IsLockedOut", 0, DbType.Int16)
                        End If
                        DAP.AddParameter("@LastLockoutDate", LastLockoutDate, DbType.DateTime)
                        DAP.AddParameter("@FailedPasswordAttemptCount", FailedPasswordAttemptCount, DbType.Int64)
                        DAP.AddParameter("@FailedPasswordAttemptWindowStart", FailedPasswordAttemptWindowStart, DbType.DateTime)
                        DAP.AddParameter("@FailedPasswordAnswerAttemptCount", FailedPasswordAnswerAttemptCount, DbType.Int64)
                        DAP.AddParameter("@FailedPasswordAnswerWindowStart", FailedPasswordAnswerAttemptWindowStart, DbType.DateTime)
                    End If
                    If errorCode = 0 Then
                        Return Password & "," & PasswordFormat
                    Else
                        Return errorCode.ToString
                    End If
                End If
            End If
        Finally
            DAP.Dispose()
        End Try
    End Function

    Private Function Membership_GetUserByUserId(ByVal UserId As String, ByVal CurrentTimeUtc As DateTime, _
    ByVal UpdateLastActivity As Boolean) As System.Web.Security.MembershipUser
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        Try
            Dim sSQL As String
            If UpdateLastActivity Then
                sSQL = "UPDATE " & _TablePrefix & "_Users " & _
                "SET LastActivityDate = ? " & _
                "WHERE UserId = ?"
                DAP.AddParameter("@LastActivityDate", CurrentTimeUtc, DbType.DateTime)
                DAP.AddParameter("@UserId", UserId, DbType.String)
                DAP.ExecuteNonQuery(sSQL)
            End If
            sSQL = "SELECT  m.Email, m.PasswordQuestion, m.Comment, m.IsApproved, " & _
            "m.CreateDate, m.LastLoginDate, u.LastActivityDate, " & _
            "m.LastPasswordChangedDate, u.UserName, m.IsLockedOut, " & _
            "m.LastLockoutDate " & _
            "FROM " & _TablePrefix & "_Users u, " & _TablePrefix & "_Membership m " & _
            "WHERE u.UserId = ? AND u.UserId = m.UserId"
            DAP.AddParameter("@UserId", UserId, DbType.String)
            Dim result As New Generic.List(Of Common.DbDataRecord)
            result = DAP.ExecuteReader(sSQL)
            If result.Count = 0 Then
                Return Nothing
            Else
                Return GetUserFromReader(result.Item(0))
            End If
        Finally
            DAP.Dispose()
        End Try
    End Function

    Private Function Membership_GetUserByName(ByVal ApplicationName As String, _
    ByVal Username As String, ByVal CurrentTimeUtc As DateTime, ByVal UpdateLastActivity As Boolean) As Common.DbDataRecord
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        Try
            Dim sSQL As String
            sSQL = "SELECT TOP 1 m.Email, m.PasswordQuestion, m.Comment, m.IsApproved, " & _
                    "m.CreateDate, m.LastLoginDate, m.LastPasswordChangedDate, u.LastActivityDate, " & _
                    "u.UserId, u.Username, m.IsLockedOut, m.LastLockoutDate " & _
                    "FROM " & _TablePrefix & "_Users u, " & _TablePrefix & "_Membership m " & _
                    "WHERE u.ApplicationId = ? " & _
                    "AND u.LoweredUserName = ? AND u.UserId = m.UserId"
            DAP.AddParameter("@ApplicationId", _AppId, DbType.String)
            DAP.AddParameter("@LoweredUserName", Username.ToLower, DbType.String)
            Dim result As New Generic.List(Of Common.DbDataRecord)
            result = DAP.ExecuteReader(sSQL)
            If result.Count = 1 Then
                If UpdateLastActivity Then
                    sSQL = "UPDATE " & _TablePrefix & "_Users " & _
                    "SET LastActivityDate = ? " & _
                    "WHERE UserId = ?"
                    DAP.AddParameter("@LastActivityDate", CurrentTimeUtc, DbType.DateTime)
                    DAP.AddParameter("@UserId", result.Item(0).Item("UserId"), DbType.String)
                    DAP.ExecuteNonQuery(sSQL)
                End If
                Return result.Item(0)
            Else
                Return Nothing
            End If
        Finally
            DAP.Dispose()
        End Try
    End Function

    Private Function Membership_GetUserByEmail(ByVal ApplicationName As String, ByVal Email As String) As String
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        Try
            Dim sSQL As String = "SELECT  u.UserName " & _
            "FROM " & _TablePrefix & "_Applications a, " & _TablePrefix & "_Users u, " & _
            _TablePrefix & "_Membership m " & _
            "WHERE a.LoweredApplicationName = ? " & _
            "AND u.ApplicationId = a.ApplicationId " & _
            "AND u.UserId = m.UserId "
            If String.IsNullOrEmpty(Email) Then
                sSQL = sSQL & "AND m.LoweredEmail IS NULL"
            Else
                sSQL = sSQL & "AND m.LoweredEmail = ?"
            End If
            DAP.AddParameter("@LoweredApplicationName", ApplicationName.ToLower, DbType.String)
            If String.IsNullOrEmpty(Email) = False Then
                DAP.AddParameter("@LoweredEmail", Email.ToLower, DbType.String)
            End If
            Dim result As New Generic.List(Of Common.DbDataRecord)
            result = DAP.ExecuteReader(sSQL)
            If (RequiresUniqueEmail AndAlso result.Count > 1) Then
                Throw New ProviderException("Membership more than one user with email " & Email)
            Else
                Return result.Item(0).GetString("UserName")
            End If
        Finally
            DAP.Dispose()
        End Try
    End Function

    Private Function Membership_ResetPassword(ByVal ApplicationName As String, ByVal UserName As String, ByVal NewPassword As String, _
    ByVal MaxInvalidPasswordAttempts As Integer, ByVal PasswordAttemptWindow As Integer, ByVal PasswordSalt As String, ByVal CurrentTimeUtc As DateTime, _
    ByVal PasswordFormat As Integer, ByVal PasswordAnswer As String) As Integer
        Dim errorCode As Integer = 0
        Dim UserId As String = Nothing
        Dim IsLockedOut As Boolean
        Dim LastLockoutDate As DateTime
        Dim FailedPasswordAttemptCount As Integer
        Dim FailedPasswordAttemptWindowStart As DateTime
        Dim FailedPasswordAnswerAttemptCount As Integer
        Dim FailedPasswordAnswerAttemptWindowStart As DateTime

        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        Try
            Dim sSQL As String
            sSQL = "SELECT UserId FROM " & _TablePrefix & "_Users " & _
            "WHERE LoweredUserName = ? AND ApplicationId = ?"
            DAP.AddParameter("@LoweredUserName", UserName.ToLower, DbType.String)
            DAP.AddParameter("@ApplicationId", _AppId, DbType.String)
            UserId = DAP.ExecuteScalar(sSQL)
            If String.IsNullOrEmpty(UserId) Then
                errorCode = 1
            Else
                sSQL = "SELECT IsLockedOut, LastLockoutDate, FailedPasswordAttemptCount, " & _
                "FailedPasswordAttemptWindowStart, FailedPasswordAnswerAttemptCount, FailedPasswordAnswerAttemptWindowStart " & _
                "FROM " & _TablePrefix & "_Membership " & _
                "WHERE UserId = ?"
                DAP.AddParameter("@UserId", UserId, DbType.String)
                Dim result1 As New Generic.List(Of Common.DbDataRecord)
                result1 = DAP.ExecuteReader(sSQL)
                IsLockedOut = result1.Item(0).GetBoolean(0)
                LastLockoutDate = result1.Item(0).GetDateTime(1)
                FailedPasswordAttemptCount = result1.Item(0).GetValue(2)
                FailedPasswordAttemptWindowStart = result1.Item(0).GetDateTime(3)
                FailedPasswordAnswerAttemptCount = result1.Item(0).GetValue(4)
                FailedPasswordAnswerAttemptWindowStart = result1.Item(0).GetDateTime(5)
                If IsLockedOut Then
                    errorCode = 99
                Else
                    sSQL = "UPDATE " & _TablePrefix & "_Membership " & _
                    "SET [Password] = ?, LastPasswordChangedDate = ?, PasswordFormat = ?, " & _
                    "PasswordSalt = ? " & _
                    "WHERE UserId = ? "
                    DAP.AddParameter("@Password", NewPassword, DbType.String)
                    DAP.AddParameter("@LastPasswordChangedDate", CurrentTimeUtc, DbType.DateTime)
                    DAP.AddParameter("@PasswordFormat", PasswordFormat, DbType.Int64)
                    DAP.AddParameter("@PasswordSalt", PasswordSalt, DbType.String)
                    DAP.AddParameter("@UserId", UserId, DbType.String)
                    If PasswordAnswer Is Nothing Then
                        sSQL = sSQL & "AND PasswordAnswer IS NULL"
                    Else
                        sSQL = sSQL & "AND PasswordAnswer = ?"
                        DAP.AddParameter("@PasswordAnswer", PasswordAnswer, DbType.String)
                    End If

                    Dim updateCount As Integer = DAP.ExecuteNonQuery(sSQL)
                    If updateCount = 0 Then
                        If CurrentTimeUtc > FailedPasswordAnswerAttemptWindowStart.AddMinutes(PasswordAttemptWindow) Then
                            FailedPasswordAnswerAttemptWindowStart = CurrentTimeUtc
                            FailedPasswordAnswerAttemptCount = 1
                        Else
                            FailedPasswordAnswerAttemptWindowStart = CurrentTimeUtc
                            FailedPasswordAnswerAttemptCount = (FailedPasswordAnswerAttemptCount + 1)
                        End If

                        If FailedPasswordAnswerAttemptCount >= MaxInvalidPasswordAttempts Then
                            IsLockedOut = True
                            LastLockoutDate = CurrentTimeUtc
                        End If
                        errorCode = 3
                    Else
                        If FailedPasswordAnswerAttemptCount > 0 Then
                            FailedPasswordAnswerAttemptCount = 0
                            FailedPasswordAnswerAttemptWindowStart = #1/1/1754#
                        End If
                    End If
                    If String.IsNullOrEmpty(PasswordAnswer) = False Then
                        sSQL = "UPDATE " & _TablePrefix & "_Membership " & _
                        "SET IsLockedOut = ?, LastLockoutDate = ?, " & _
                        "FailedPasswordAttemptCount = ?, FailedPasswordAttemptWindowStart = ?, " & _
                        "FailedPasswordAnswerAttemptCount = ?, " & _
                        "FailedPasswordAnswerAttemptWindowStart = ? " & _
                        "WHERE UserId = ?"
                        If IsLockedOut Then
                            DAP.AddParameter("@IsLockedOut", 1, DbType.Int16)
                        Else
                            DAP.AddParameter("@IsLockedOut", 0, DbType.Int16)
                        End If
                        DAP.AddParameter("@LastLockoutDate", LastLockoutDate, DbType.DateTime)
                        DAP.AddParameter("@FailedPasswordAttemptCount", FailedPasswordAttemptCount, DbType.Int64)
                        DAP.AddParameter("@FailedPasswordAttemptWindowStart", FailedPasswordAttemptWindowStart, DbType.DateTime)
                        DAP.AddParameter("@FailedPasswordAnswerAttemptCount", FailedPasswordAnswerAttemptCount, DbType.Int64)
                        DAP.AddParameter("@FailedPasswordAnswerAttemptWindowStart", FailedPasswordAnswerAttemptWindowStart, DbType.DateTime)
                        DAP.AddParameter("@UserId", UserId, DbType.String)
                        DAP.ExecuteNonQuery(sSQL)
                    End If
                End If
            End If
            Return errorCode
        Finally
            DAP.Dispose()
        End Try
    End Function

    Private Function Membership_UnlockUser(ByVal ApplicationName As String, ByVal UserName As String) As Integer
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        Try
            Dim UserId As String = Nothing
            Dim sSQL As String
            sSQL = "SELECT u.UserId " & _
            "FROM " & _TablePrefix & "_Users u, " & _TablePrefix & "_Applications a, " & _
            _TablePrefix & "_Membership m " & _
            "WHERE LoweredUserName = ? " & _
            "AND u.ApplicationId = a.ApplicationId " & _
            "AND a.LoweredApplicationName = ? " & _
            "u.UserId = m.UserId"
            UserId = DAP.ExecuteScalar(sSQL)
            If String.IsNullOrEmpty(UserId) Then
                Return 1
            End If
            sSQL = "UPDATE " & _TablePrefix & "_Membership " & _
            "SET IsLockedOut = 0," & _
            "FailedPasswordAttemptCount = 0," & _
            "FailedPasswordAttemptWindowStart = ?," & _
            "FailedPasswordAnswerAttemptCount = 0," & _
            "FailedPasswordAnswerAttemptWindowStart = ?," & _
            "LastLockoutDate = ?" & _
            "WHERE UserId = ?"
            DAP.AddParameter("@FailedPasswordAttemptWindowStart", #1/1/1754#, DbType.DateTime)
            DAP.AddParameter("@FailedPasswordAnswerAttemptWindowStart", #1/1/1754#, DbType.DateTime)
            DAP.AddParameter("@LastLockoutDate", #1/1/1754#, DbType.DateTime)
            DAP.AddParameter("@UserId", UserId, DbType.String)
            If DAP.ExecuteNonQuery(sSQL) = 1 Then
                Return 0
            Else
                Return 1
            End If
        Finally
            DAP.Dispose()
        End Try
    End Function

    Private Function Membership_UpdateUser(ByVal ApplicationName As String, ByVal UserName As String, _
    ByVal Email As String, ByVal Comment As String, ByVal IsApproved As Boolean, ByVal LastLoginDate As DateTime, _
    ByVal LastActivityDate As DateTime, ByVal UniqueEmail As Integer, ByVal CurrentTimeUtc As DateTime) As Integer
        Dim UserId As String = Nothing
        Dim ApplicationId As String = Nothing
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        Try
            Dim sSQL As String
            sSQL = "SELECT u.UserId, a.ApplicationId " & _
            "FROM " & _TablePrefix & "_Users u, " & _TablePrefix & "_Applications a, " & _
            _TablePrefix & "_Membership m " & _
            "WHERE LoweredUserName = ? " & _
            "AND u.ApplicationId = a.ApplicationId " & _
            "AND a.LoweredApplicationName = ? " & _
            "AND u.UserId = m.UserId"
            DAP.AddParameter("@LoweredUserName", UserName.ToLower, DbType.String)
            DAP.AddParameter("@LoweredApplicationName", _ApplicationName, DbType.String)
            Dim result As New Generic.List(Of Common.DbDataRecord)
            result = DAP.ExecuteReader(sSQL)
            If result.Count > 1 Or (String.IsNullOrEmpty(result.Item(0).GetString(0))) Then
                Return 1
            Else
                UserId = result.Item(0).GetString(0)
                ApplicationId = result.Item(0).GetString(1)
                If UniqueEmail Then
                    sSQL = "SELECT COUNT(1) As EmailCount " & _
                    "FROM " & _TablePrefix & "_Membership " & _
                    "WHERE ApplicationId = ? " & _
                    "AND UserId <> ? " & _
                    "AND LoweredEmail = ?"
                    DAP.AddParameter("@ApplicationId", ApplicationId, DbType.String)
                    DAP.AddParameter("@UserId", UserId, DbType.String)
                    DAP.AddParameter("@LoweredEmail", Email.ToLower, DbType.String)
                    If DAP.ExecuteScalar(sSQL) > 0 Then
                        Return 7
                    End If
                End If

                sSQL = "UPDATE " & _TablePrefix & "_Users " & _
                "SET LastActivityDate = ? " & _
                "WHERE UserId = ?"
                DAP.AddParameter("@LastActivityDate", CurrentTimeUtc, DbType.DateTime)
                DAP.AddParameter("@UserId", UserId, DbType.String)
                If DAP.ExecuteNonQuery(sSQL) <> 1 Then
                    Return -1
                End If

                sSQL = "UPDATE " & _TablePrefix & "_Membership " & _
                "SET Email = ?, LoweredEmail = ?, Comment = ?, " & _
                "IsApproved = ?, LastLoginDate = ? " & _
                "WHERE UserId = ?"
                DAP.AddParameter("@Email", Email, DbType.String)
                DAP.AddParameter("@LoweredEmail", Email.ToLower, DbType.String)
                DAP.AddParameter("@Comment", Comment, DbType.String)
                DAP.AddParameter("@IsApproved", IsApproved, DbType.UInt16)
                DAP.AddParameter("@LastLoginDate", LastLoginDate, DbType.DateTime)
                DAP.AddParameter("@UserId", UserId, DbType.String)
                If DAP.ExecuteNonQuery(sSQL) = 1 Then
                    Return 0
                Else
                    Return -1
                End If
            End If
        Finally
            DAP.Dispose()
        End Try
    End Function

    Private Function Membership_UpdateUserInfo(ByVal Username As String, ByVal IsPasswordCorrect As Boolean, _
    ByVal UpdateLastLoginActivityDate As Boolean, ByVal MaxInvalidPasswordAttempts As Integer, _
    ByVal PasswordAttemptWindow As Integer, ByVal CurrentTimeUtc As DateTime, ByVal LastLoginDate As DateTime, _
    ByVal LastActivityDate As DateTime) As Integer
        Dim errorCode As Integer
        Dim UserId As String = String.Empty
        Dim IsApproved As Boolean
        Dim IsLockedOut As Boolean
        Dim LastLockoutDate As DateTime
        Dim FailedPasswordAttemptCount As Integer
        Dim FailedPasswordAttemptWindowStart As DateTime
        Dim FailedPasswordAnswerAttemptCount As Integer
        Dim FailedPasswordAnswerAttemptWindowStart As DateTime
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        Try
            Dim sSQL As String
            sSQL = "SELECT u.UserId, " & _
            "m.IsApproved, m.IsLockedOut, m.LastLockoutDate, " & _
            "m.FailedPasswordAttemptCount, m.FailedPasswordAttemptWindowStart, " & _
            "m.FailedPasswordAnswerAttemptCount, m.FailedPasswordAnswerAttemptWindowStart " & _
            "FROM " & _TablePrefix & "_Users u, " & _TablePrefix & "_Membership m " & _
            "WHERE u.ApplicationId = ? " & _
            "AND u.UserId = m.UserId AND u.LoweredUserName = ?"
            DAP.AddParameter("@ApplicationId", _AppId, DbType.String)
            DAP.AddParameter("@LoweredUserName", Username.ToLower, DbType.String)
            Dim result As New Generic.List(Of Common.DbDataRecord)
            result = DAP.ExecuteReader(sSQL)
            If result.Count = 1 Then
                UserId = result.Item(0).GetValue(0)
                IsApproved = result.Item(0).GetBoolean(1)
                IsLockedOut = result.Item(0).GetBoolean(2)
                LastLockoutDate = result.Item(0).GetDateTime(3)
                FailedPasswordAttemptCount = result.Item(0).GetValue(4)
                FailedPasswordAttemptWindowStart = result.Item(0).GetDateTime(5)
                FailedPasswordAnswerAttemptCount = result.Item(0).GetValue(6)
                FailedPasswordAnswerAttemptWindowStart = result.Item(0).GetDateTime(7)
            Else
                errorCode = 1
            End If
            If IsApproved = False Then
                errorCode = 0
                Return errorCode
            End If
            If IsLockedOut Then
                errorCode = 0
                Return errorCode
            End If
            If IsPasswordCorrect = False Then
                If CurrentTimeUtc > FailedPasswordAttemptWindowStart.AddMinutes(PasswordAttemptWindow) Then
                    FailedPasswordAttemptWindowStart = CurrentTimeUtc
                    FailedPasswordAttemptCount = 1
                Else
                    FailedPasswordAttemptWindowStart = CurrentTimeUtc
                    FailedPasswordAttemptCount = (FailedPasswordAttemptCount + 1)
                End If

                If (FailedPasswordAttemptCount >= MaxInvalidPasswordAttempts) Then
                    IsLockedOut = True
                    LastLockoutDate = CurrentTimeUtc
                End If
            Else
                If (FailedPasswordAttemptCount > 0 Or FailedPasswordAnswerAttemptCount > 0) Then
                    FailedPasswordAttemptCount = 0
                    FailedPasswordAttemptWindowStart = #1/1/1754#
                    FailedPasswordAnswerAttemptCount = 0
                    FailedPasswordAnswerAttemptWindowStart = #1/1/1754#
                    LastLockoutDate = #1/1/1754#
                End If
            End If
            If UpdateLastLoginActivityDate Then
                sSQL = "UPDATE " & _TablePrefix & "_Users " & _
                "SET LastActivityDate = ? " & _
                "WHERE UserId = ?"
                DAP.AddParameter("@LastActivityDate", LastActivityDate, DbType.DateTime)
                DAP.AddParameter("@UserId", UserId, DbType.String)
                DAP.ExecuteNonQuery(sSQL)

                sSQL = "UPDATE " & _TablePrefix & "_Membership " & _
                "SET LastLoginDate = ? " & _
                "WHERE UserId = ?"
                DAP.AddParameter("@LastLoginDate", LastLoginDate, DbType.DateTime)
                DAP.AddParameter("@UserId", UserId, DbType.String)
                DAP.ExecuteNonQuery(sSQL)
            End If

            sSQL = "UPDATE " & _TablePrefix & "_Membership " & _
            "SET IsLockedOut = ?, LastLockoutDate = ?, " & _
            "FailedPasswordAttemptCount = ?, " & _
            "FailedPasswordAttemptWindowStart = ?, " & _
            "FailedPasswordAnswerAttemptCount = ?, " & _
            "FailedPasswordAnswerAttemptWindowStart = ? " & _
            "WHERE UserId = ?"
            DAP.AddParameter("@IsLockedOut", IsLockedOut, DbType.Int16)
            DAP.AddParameter("@LastLockoutDate", LastLockoutDate, DbType.DateTime)
            DAP.AddParameter("@FailedPasswordAttemptCount", FailedPasswordAttemptCount, DbType.Int64)
            DAP.AddParameter("@FailedPasswordAttemptWindowStart", FailedPasswordAttemptWindowStart, DbType.DateTime)
            DAP.AddParameter("@FailedPasswordAnswerAttemptCount", FailedPasswordAnswerAttemptCount, DbType.Int64)
            DAP.AddParameter("@FailedPasswordAnswerAttemptWindowStart", FailedPasswordAnswerAttemptWindowStart, DbType.DateTime)
            DAP.AddParameter("@UserId", UserId, DbType.String)
            If DAP.ExecuteNonQuery(sSQL) = 1 Then
                errorCode = 0
            Else
                errorCode = 1
            End If
        Finally
            DAP.Dispose()
        End Try
    End Function

    Private Function Membership_GetPasswordWithFormat(ByVal Username As String, ByVal UpdateLastLoginActivityDate As Boolean, _
    ByVal CurrentTimeUtc As DateTime) As Common.DbDataRecord
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        Try
            Dim errorCode As Integer
            Dim IsLockedOut As Boolean
            Dim UserId As String = String.Empty
            Dim Password As String
            Dim PasswordSalt As String
            Dim PasswordFormat As Integer
            Dim FailedPasswordAttemptCount As Integer
            Dim FailedPasswordAnswerAttemptCount As Integer
            Dim IsApproved As Boolean
            Dim LastActivityDate As DateTime
            Dim LastLoginDate As DateTime

            Dim sSQL As String
            sSQL = "SELECT u.UserId, m.IsLockedOut, Password, PasswordFormat, " & _
            "PasswordSalt, FailedPasswordAttemptCount, " & _
            "FailedPasswordAnswerAttemptCount, IsApproved, " & _
            "LastActivityDate, LastLoginDate " & _
            "FROM " & _TablePrefix & "_Users u, " & _TablePrefix & "_Membership m " & _
            "WHERE u.ApplicationId = ? " & _
            "AND u.UserId = m.UserId " & _
            "AND u.LoweredUserName = ?"
            DAP.AddParameter("@Applicationid", _AppId, DbType.String)
            DAP.AddParameter("@LoweredUserName", Username.ToLower, DbType.String)
            Dim result As New Generic.List(Of Common.DbDataRecord)
            result = DAP.ExecuteReader(sSQL)
            If result.Count = 1 Then
                UserId = result.Item(0).GetString(0)
                IsLockedOut = result.Item(0).GetBoolean(1)
                Password = result.Item(0).GetString(2)
                PasswordFormat = result.Item(0).GetValue(3)
                PasswordSalt = result.Item(0).GetString(4)
                FailedPasswordAttemptCount = result.Item(0).GetValue(5)
                FailedPasswordAnswerAttemptCount = result.Item(0).GetValue(6)
                IsApproved = result.Item(0).GetBoolean(7)
                LastActivityDate = result.Item(0).GetDateTime(8)
                LastLoginDate = result.Item(0).GetDateTime(9)

                If String.IsNullOrEmpty(UserId) Then
                    errorCode = 1
                End If

                If IsLockedOut Then
                    errorCode = 99
                End If

                If (UpdateLastLoginActivityDate = 1 And IsApproved) Then
                    sSQL = "UPDATE " & _TablePrefix & "_Membership " & _
                    "SET LastLoginDate = ? " & _
                    "WHERE UserId = ?"
                    DAP.AddParameter("@LastLoginDate", LastLoginDate, DbType.DateTime)
                    DAP.AddParameter("@UserId", UserId, DbType.String)
                    DAP.ExecuteNonQuery(sSQL)

                    sSQL = "UPDATE " & _TablePrefix & "_Users " & _
                    "SET LastActivityDate = ? " & _
                    "WHERE UserId = ?"
                    DAP.AddParameter("@LastLoginDate", LastActivityDate, DbType.DateTime)
                    DAP.AddParameter("@UserId", UserId, DbType.String)
                    DAP.ExecuteNonQuery(sSQL)
                End If
                Return result.Item(0)
            Else
                errorCode = 1
                Return Nothing
            End If
        Finally

        End Try
    End Function
#End Region
End Class
